﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using FarPoint.Win.Spread.CellType;
using System.Globalization;
using Spread.Common.DataStore;
using Spread.Common.DataStore.Models;
using FarPoint.Win.Spread;
using FarPoint.Win;
using GrapeCity.BusinessKPI.Sparkline;
using FarPoint.Win.Spread.Chart;

namespace GrapeCity.BusinessKPI
{
    public partial class BusinessKPI : Spread.Common.Features.Sample.BaseSampleControl
    {
        #region Fields
        private string _circularSymbol;
        private Color _themeColor;

        private KPIDataSource _dataSource;
        private string[] _trendTypes;
        private KPIList _kpiList;
        private List<TargetMaster> _targetMasterList;
        private List<TargetMaster> _currentYearMasterList;
        private List<TargetMaster> _previousYearMasterList;

        private ExcelSparklineSetting _exSetting;
        private NumberCellType _numCell;
        private NumberCellType _decimalCell;
        private CurrencyCellType _currencyCell;
        private PercentCellType _percentCell;
        private ComboBoxCellType comboCellMonth = null;
        private DateTimeFormatInfo _dateFormatInfo;
        private DateTime _date;
        #endregion
        #region ctor
        public BusinessKPI()
        {
            InitializeComponent();

            _circularSymbol = "●";
            _themeColor = Color.FromArgb(30, 122, 197);
            _dataSource = new DataService().KPIDataSource;
            //Get data 
            _kpiList = _dataSource.GetAllKPIs();
            _targetMasterList = _dataSource.GetAllTargets();

            _dateFormatInfo = new CultureInfo("en-US").DateTimeFormat;
            _trendTypes = new string[] { "ytd", "ytd", "%", "%", "ytd", "%", "yoy", "yoy", "%", "%" };

            _exSetting = new ExcelSparklineSetting
            {
                DisplayHidden = true,
            };
            //Create NumberCellType
            _numCell = new NumberCellType
            {
                DecimalPlaces = 0
            };

            _decimalCell = new NumberCellType
            {
                DecimalPlaces = 2,
                FocusPosition = EditorFocusCursorPosition.End
            };

            //Create CurrencyCellType
            _currencyCell = new CurrencyCellType
            {
                CurrencySymbol = "$",
                DecimalPlaces = 2,
                FocusPosition = EditorFocusCursorPosition.End
            };

            //Create PercentCellType
            _percentCell = new PercentCellType
            {
                DecimalPlaces = 2,
                PercentSign = "%",
                FocusPosition = EditorFocusCursorPosition.End
            };

            this.Load += BusinessKPI_Load;
        }

        #endregion
        #region Event handlers
        private void BusinessKPI_Load(object sender, EventArgs e)
        {
            SetSheetProperties(fpSpread.ActiveSheet);
            SetDataSheetColors(fpSpread.ActiveSheet, 15, 0);
            AddComboSelector(fpSpread.ActiveSheet, 1, 2);
            // Load data in _currentYearMasterList and _previousYearMasterList
            LoadSelectedMetrics();
            SetDataSheetProperties(fpSpread.ActiveSheet, 15, 0);
            // Load data in KPI table
            LoadDataSheet(fpSpread.ActiveSheet, 19, 0);
            LoadKPIList(fpSpread.ActiveSheet, 4, 2);
            LoadMetricMeasures(fpSpread.ActiveSheet, 4, 5);
            LoadMetrics(fpSpread.ActiveSheet, 4, 9);
            LoadTargetSparklines(fpSpread.ActiveSheet, 4, 11);
            LoadTrendSparklines(fpSpread.ActiveSheet, 4, 6);
            LoadCircularIcons(fpSpread.ActiveSheet, 4, 1);
        }
        private void ComboCell_EditingStopped(object sender, EventArgs e)
        {
            LoadSelectedMetrics();
            LoadYearlyDataHeaders(fpSpread.ActiveSheet, 16, 0);
            LoadDataSheet(fpSpread.ActiveSheet, 19, 0);
            LoadMetrics(fpSpread.ActiveSheet, 4, 9);

            if (_currentYearMasterList.Where(l => l.Month == _date.Month).Any())
            {
                LoadTargetSparklines(fpSpread.ActiveSheet, 4, 11);
                LoadTrendSparklines(fpSpread.ActiveSheet, 4, 6);
                LoadCircularIcons(fpSpread.ActiveSheet, 4, 1);
            }
        }
        #endregion
        #region Methods
        // Set properties and header for tabular data
        private void SetDataSheetProperties(SheetView sheet, int row, int col)
        {
            sheet.Rows[row].Height = 40;
            sheet.Rows[row].Font = new Font("Segoe UI", 14, FontStyle.Bold);
            sheet.Cells[row, col].HorizontalAlignment = CellHorizontalAlignment.Left;
            sheet.AddSpanCell(row, col, 1, sheet.ColumnCount);

            sheet.Rows[row + 17].Height = 40;
            sheet.Rows[row + 17].Font = new Font("Segoe UI", 14, FontStyle.Bold);
            sheet.Cells[row + 17, col].HorizontalAlignment = CellHorizontalAlignment.Left;
            sheet.AddSpanCell(row + 17, col, 1, sheet.ColumnCount);

            sheet.AlternatingRows.Count = 2;
            sheet.AlternatingRows[0].BackColor = Color.FromArgb(246, 246, 246);
            sheet.AlternatingRows[1].BackColor = Color.FromArgb(245, 245, 222);

            sheet.Cells[row + 4, col + 1, row + 15, sheet.ColumnCount - 1].Locked = false;
            sheet.Cells[row + 21, col + 1, row + 32, sheet.ColumnCount - 1].Locked = false;

            LoadYearlyDataHeaders(sheet, row + 1, col);

            AddSpanCellsToFirstColumn(sheet, 16, 0, 31);
            AddSpanCellsToFirstColumn(sheet, 33, 0, 47);
            SetDataSheetCellTypes(sheet, row + 4, col + 2);
            SetDataSheetCellTypes(sheet, row + 21, col + 2);

            sheet.Rows[14].BackColor = Color.White;
            sheet.Rows[14].Border = new LineBorder(Color.White, 2, true, false, true, false);
            sheet.Rows[31].BackColor = Color.White;
            sheet.Rows[31].Border = new LineBorder(Color.White, 2, true, false, true, false);
        }

        // Set CellTypes for columns in KPI data table
        private void SetDataSheetCellTypes(SheetView sheet, int row, int col)
        {
            sheet.Cells[row, col, row + 11, col + 1].CellType = _numCell;
            sheet.Cells[row, col + 2, row + 11, col + 3].CellType = _currencyCell;
            sheet.Cells[row, col + 4, row + 11, col + 5].CellType = _percentCell;
            sheet.Cells[row, col + 6, row + 11, col + 7].CellType = _percentCell;
            sheet.Cells[row, col + 8, row + 11, col + 9].CellType = _currencyCell;
            sheet.Cells[row, col + 10, row + 11, col + 11].CellType = _percentCell;
            sheet.Cells[row, col + 12, row + 11, col + 13].CellType = _numCell;
            sheet.Cells[row, col + 14, row + 11, col + 15].CellType = _currencyCell;
            sheet.Cells[row, col + 16, row + 11, col + 19].CellType = _percentCell;
        }

        // Load tabular data for Current and Previous Year
        private void LoadDataSheet(SheetView sheet, int row, int col)
        {
            LoadYearlyData(sheet, row, col, _currentYearMasterList, new DateTime(_date.Year, 1, 1));
            LoadYearlyData(sheet, row + 17, col, _previousYearMasterList, new DateTime(_date.Year - 1, 1, 1));
        }

        // Set headers for both KPI data tables
        private void LoadYearlyDataHeaders(SheetView sheet, int row, int col)
        {
            // For current Year
            LoadYearlyDataHeaders(sheet, row, col, new DateTime(_date.Year, 1, 1));

            // For previous Year
            LoadYearlyDataHeaders(sheet, row + 17, col, new DateTime(_date.Year - 1, 1, 1));
        }

        // Set BackColor and ForeColors Tabular data
        private void SetDataSheetColors(SheetView sheet, int row, int col)
        {
            sheet.Rows[row].BackColor = _themeColor;
            sheet.Rows[row, row + 3].ForeColor = Color.WhiteSmoke;
            sheet.Rows[row + 1, row + 3].BackColor = Color.FromArgb(150, 150, 150);
            sheet.Cells[row + 4, col, row + 15, 0].BackColor = Color.FromArgb(150, 150, 150);
            sheet.Cells[row + 4, col, row + 15, 0].ForeColor = Color.WhiteSmoke;

            sheet.Rows[row + 17].BackColor = _themeColor;
            sheet.Rows[row + 17, row + 20].ForeColor = Color.WhiteSmoke;
            sheet.Rows[row + 18, row + 20].BackColor = Color.FromArgb(150, 150, 150);
            sheet.Cells[row + 21, col, row + 32, 0].BackColor = Color.FromArgb(150, 150, 150);
            sheet.Cells[row + 21, col, row + 32, 0].ForeColor = Color.WhiteSmoke;
        }

        // Add SpanCells in given Column, and Range of Rows
        private void AddSpanCellsToFirstColumn(SheetView sheet, int startRow, int col, int endRow)
        {
            for (int i = startRow; i <= endRow; ++i)
            {
                sheet.AddSpanCell(i, col, 1, 2);
            }
        }

        // Set headers for KPI data table
        private void LoadYearlyDataHeaders(SheetView sheet, int row, int col, DateTime startDate)
        {
            sheet.Cells[row, col].Value = "KPI ID";
            sheet.Cells[row + 1, col].Value = "KPI Name";

            for (int i = 1; i <= _kpiList.Items.Count; ++i)
            {
                sheet.AddSpanCell(row, col + i * 2, 1, 2);
                sheet.Cells[row, col + i * 2].Value = i;
                sheet.AddSpanCell(row + 1, col + i * 2, 1, 2);
                sheet.Cells[row + 1, col + i * 2].Value = _kpiList.Items[i];

                sheet.Cells[row + 2, col + i * 2].Value = "Actual";
                sheet.Cells[row + 2, col + i * 2 + 1].Value = "Target";
            }
        }

        // Load KPI data in the table
        private void LoadYearlyData(SheetView sheet, int row, int col, List<TargetMaster> targetMasterList,
            DateTime startDate)
        {
            // Clear Previous data
            sheet.Cells[row, col, row + 11, sheet.ColumnCount - 1].ResetValue();

            if (targetMasterList.Count == 0)
            {
                return;
            }
            sheet.Cells[row - 4, col].Value = $"KPI Data for the year {startDate.Year}";

            DateTime date = startDate;
            int _rowCount = 0;
            // Set months as RowHeader for tabular data
            while (date.Year == startDate.Year)
            {
                sheet.Cells[row + _rowCount, col].Value = date.ToString("MMM, yyyy");
                ++_rowCount;
                date = date.AddMonths(1);
            }

            _rowCount = row;
            // Set actual and target values in corresponding cells for each Month
            foreach (TargetMaster targetMaster in targetMasterList)
            {
                int _colCount = 1;
                foreach (KPIMetrics target in targetMaster.KPITargets)
                {
                    sheet.Cells[_rowCount, col + _colCount * 2].Value = targetMaster.KPITargets[_colCount - 1].Actual;
                    sheet.Cells[_rowCount, col + _colCount * 2 + 1].Value = targetMaster.KPITargets[_colCount - 1].Target;
                    ++_colCount;
                }
                ++_rowCount;
            }
        }

        private void SetSheetProperties(SheetView sheet)
        {
            sheet.ColumnHeader.Visible = false;
            sheet.RowHeader.Visible = false;
            sheet.RowCount = 48;
            sheet.ColumnCount = 22;

            sheet.Columns[0, 1].Width = 50;
            sheet.Columns[2, 20].Width = 87;

            sheet.Cells[0, 0, sheet.RowCount - 1, sheet.ColumnCount - 1].HorizontalAlignment = CellHorizontalAlignment.Center;
            sheet.Cells[0, 0, sheet.RowCount - 1, sheet.ColumnCount - 1].VerticalAlignment = CellVerticalAlignment.Center;
            sheet.Cells[0, 0, sheet.RowCount - 1, sheet.ColumnCount - 1].Locked = true;
            // Title for sample
            sheet.AddSpanCell(0, 0, 2, 12);
            sheet.Cells[0, 0].HorizontalAlignment = CellHorizontalAlignment.Left;
            sheet.Cells[0, 0].Text = "Business KPI";
            sheet.Cells[0, 0].Font = new Font("Segoe UI", 16, FontStyle.Bold);
            sheet.Rows[0, 2].BackColor = _themeColor;
            sheet.Rows[0, 2].ForeColor = Color.WhiteSmoke;

            sheet.Cells[0, 0, 2, sheet.ColumnCount - 1].Border = new ComplexBorder(new ComplexBorderSide(_themeColor, 2),
                new ComplexBorderSide(_themeColor, 2), new ComplexBorderSide(_themeColor, 2),
                new ComplexBorderSide(_themeColor, 2));

            // Label for Month and Year Selector
            sheet.AddSpanCell(0, 12, 1, 2);
            sheet.Cells[0, 12].Value = "Select Month & Year";
            sheet.Cells[0, 12].Font = new Font("Segoe UI", 12, FontStyle.Bold);
            sheet.Cells[0, 12].ForeColor = Color.WhiteSmoke;

            sheet.Rows[0, sheet.RowCount - 1].Height = 30;

            sheet.Cells[3, 0].ColumnSpan = 5;
            sheet.Cells[3, 0].Value = "Business KPIs";
            sheet.Cells[3, 5].Value = "Metric";
            sheet.Cells[3, 6].ColumnSpan = 3;
            sheet.Cells[3, 6].Value = "Last Year";
            sheet.Cells[3, 9].Tag = "MonthYearCell";
            sheet.Cells[3, 10].Value = "Target";
            sheet.Cells[3, 11].ColumnSpan = 4;
            sheet.Cells[3, 11].Value = "% vs. Target";
            sheet.Cells[3, 0, 3, 14].Border = new LineBorder(Color.Black, 1, false, true, false, true);

            sheet.Cells[4, 1, 13, 1].Font = new Font("Segoe UI", 16, FontStyle.Regular);
            //Span Trend and Target sparkline cells
            for (int i = 0; i < _kpiList.Items.Count; ++i)
            {
                sheet.AddSpanCell(4 + i, 6, 1, 3);
                sheet.AddSpanCell(4 + i, 11, 1, 4);
            }
            SetCellTypes(sheet, 4, 9); // CellTypes for Actual and Target columns
        }
        // CellTypes for trend cells
        private void SetCellTypes(SheetView sheet, int row, int col)
        {
            sheet.Cells[row, col, row + 9, col + 1].CellType = _percentCell;
        }

        // Add Month and Year Selector
        private void AddComboSelector(SheetView sheet, int row, int col)
        {
            sheet.Rows[row].Height = 25;

            comboCellMonth = new ComboBoxCellType
            {
                DropDownOptions = FarPoint.Win.DropDownOptions.ButtonAndText,
                Editable = false,
                Items = _dateFormatInfo.MonthNames.Take(DateTime.Now.Month).ToArray(),
                StopEditingAfterDropDownItemSelected = true
            };
            comboCellMonth.EditingStopped += ComboCell_EditingStopped;

            ComboBoxCellType comboCellYear = new ComboBoxCellType
            {
                DropDownOptions = FarPoint.Win.DropDownOptions.ButtonAndText,
                Editable = false,
                Items = new string[] { (DateTime.Now.Year - 2).ToString(), (DateTime.Now.Year - 1).ToString(), DateTime.Now.Year.ToString() },
                StopEditingAfterDropDownItemSelected = true
            };
            comboCellYear.EditingStopped += ComboCell_EditingStopped;

            // Formatting for both selectors
            sheet.Cells[row, col + 10, row, col + 11].BackColor = Color.WhiteSmoke;
            sheet.Cells[row, col + 10, row, col + 11].ForeColor = Color.Black;

            sheet.Cells[row, col + 10].CellType = comboCellMonth;
            sheet.Cells[row, col + 10].Value = DateTime.Now.ToString("MMMM");
            sheet.Cells[row, col + 10].Tag = "MonthCell";
            sheet.Cells[row, col + 10].Locked = false;

            sheet.Cells[row, col + 11].CellType = comboCellYear;
            sheet.Cells[row, col + 11].Value = DateTime.Now.ToString("yyyy");
            sheet.Cells[row, col + 11].Tag = "YearCell";
            sheet.Cells[row, col + 11].Locked = false;

            // Formatting for Row that shows error if no data is available
            sheet.Rows[row + 1].Height = 10;
            sheet.AddSpanCell(row + 1, col + 1, 1, 3);
            sheet.Cells[row + 1, col + 1].ForeColor = Color.WhiteSmoke;
            sheet.Cells[row + 1, col + 1].Font = new Font("Segoe UI", 9, FontStyle.Bold);
        }

        private void LoadTrendSparklines(SheetView sheet, int row, int col)
        {
            int srcRowStart, srcRowEnd;
            // If previous year data is not available then show trend of current year
            if (_previousYearMasterList.Count == 0)
            {
                sheet.Cells[row - 1, col].Value = "This Year (12 months)";
                srcRowStart = 19;
                srcRowEnd = 30;
            }
            else
            {
                sheet.Cells[row - 1, col].Value = "Last Year (12 months)";
                srcRowStart = 36;
                srcRowEnd = 47;
            }
            for (int i = 0; i < _kpiList.Items.Count; ++i)
            {
                AddTrendSparkline(sheet, row + i, col, srcRowStart, srcRowEnd);
            }
        }

        private void AddTrendSparkline(SheetView sheet, int row, int col, int srcRowStart, int srcRowEnd)
        {
            ExcelSparklineGroup group = new ExcelSparklineGroup();
            ExcelSparklineSetting setting = new ExcelSparklineSetting();
            ExcelSparkline sparkline = new ExcelSparkline();
            sheet.SparklineContainer.Add(group);
            group.Renderer = new LineSparklineRenderer();
            group.Setting = setting;
            // Use KPI table data as source
            sparkline.Formula = $"Sheet1!${((char)('A' + (row - 3) * 2)).ToString()}${srcRowStart + 1}:" +
                $"Sheet1!${((char)('A' + (row - 3) * 2)).ToString()}${srcRowEnd + 1}";
            setting.ShowNegative = true;
            sparkline.Row = row;
            sparkline.Column = col;
            group.Add(sparkline);
        }

        // Add Target sparklines (for comparison of Actual & Target values)
        private void LoadTargetSparklines(SheetView sheet, int row, int col)
        {
            for (int i = 0; i < _kpiList.Items.Count; ++i)
            {
                AddTargetSparkline(sheet, row + i, col);
            }
        }

        private void AddTargetSparkline(SheetView sheet, int row, int col)
        {
            // Use Actual and Target columns as source for sparkline
            TargetSparkline sparkline = new TargetSparkline(sheet, row, col,
                new SheetCellRange(sheet, row, col - 2, 1, 1), new SheetCellRange(sheet, row, col - 1, 1, 1));
            TargetSparklineGroup group = new TargetSparklineGroup();
            TargetSparklineSetting setting = new TargetSparklineSetting();
            sheet.SparklineContainer.Add(group);
            group.Setting = setting;
            group.Renderer = new TargetSparklineRenderer();
            group.Add(sparkline);
        }

        // Set cell values according to metric measures taken (ytd, yoy, %)
        private void LoadMetricMeasures(SheetView sheet, int row, int col)
        {
            foreach (string trendType in _trendTypes)
            {
                sheet.Cells[row++, col].Value = trendType;
            }
        }

        // Set KPI ID and Names
        private void LoadKPIList(SheetView sheet, int row, int col)
        {
            foreach (KeyValuePair<int, string> pair in _kpiList.Items)
            {
                sheet.Cells[row, col - 2].Value = pair.Key;
                sheet.AddSpanCell(row, col, 1, 3);
                sheet.Cells[row++, col].Value = pair.Value;
            }
        }

        // Populate _currentYearMasterList and _previousYearMasterList according to Month & Year selected
        private void LoadSelectedMetrics()
        {
            int year = int.Parse(fpSpread.Sheets[0].Cells["YearCell"].Value.ToString());
            comboCellMonth.Items = _dateFormatInfo.MonthNames.Take((DateTime.Now.Year == year ? DateTime.Now.Month : 12)).ToArray();
            if (fpSpread.Sheets[0].Cells["MonthCell"].Text == "")
            {
                fpSpread.Sheets[0].Cells["MonthCell"].Value = comboCellMonth.Items.Last();
            }

            int month = _dateFormatInfo.MonthNames.ToList().IndexOf(fpSpread.Sheets[0].Cells["MonthCell"].Value.
                ToString()) + 1;
            _date = new DateTime(year, month, 1);

            _currentYearMasterList = _targetMasterList.Where(t => t.Year == year).OrderBy(t => t.Month).ToList();
            _previousYearMasterList = _targetMasterList.Where(t => t.Year == year - 1).OrderBy(t => t.Month).ToList();

            fpSpread.Sheets[0].Cells["MonthYearCell"].Value = $"{_date.ToString("MMM")}/{_date.ToString("yyyy")}";

            // Hide Rows for Previous Year data table if previous year data is not available
            fpSpread.ActiveSheet.Rows[31, 47].Visible = (_previousYearMasterList.Count != 0);
        }

        private void ClearPreviousData(SheetView sheet, int row, int col)
        {
            sheet.Cells[2, 3].ResetValue();
            sheet.Cells[row, col, row + _kpiList.Items.Count - 1, col + 1].ResetValue();
            sheet.ClearSparklines(new FarPoint.Win.Spread.Model.CellRange(row, col - 3, 10, 1)); // Trend Sparkline
            sheet.ClearSparklines(new FarPoint.Win.Spread.Model.CellRange(row, col + 2, 10, 1)); // Target Sparkline
        }

        // Set metric values in cells
        private void LoadMetrics(SheetView sheet, int row, int col)
        {
            ClearPreviousData(sheet, row, col);

            // Show appropriate text in case data is not available for current year
            if (!_currentYearMasterList.Where(m => m.Year == _date.Year && m.Month == _date.Month).Any())
            {
                sheet.Cells[2, 3].Value = $"No data available for {_date.ToString("MMM, yyyy")}";
                return;
            }
            sheet.Cells[2, 3].Value = string.Empty;

            // Set formulae in Actual and Target columns of KPI using tabular data
            LoadActualAndTargetMetrics(sheet, row, col);
        }

        // Set formulae for Actual and Target columns
        private void LoadActualAndTargetMetrics(SheetView sheet, int row, int col)
        {
            TargetMaster previousYearTarget = _previousYearMasterList.Where(p => p.Month == _date.Month).FirstOrDefault();
            for (int i = 1; i <= _trendTypes.Count(); ++i)
            {
                string actualFormula = string.Empty;
                string targetFormula = string.Empty;
                // Take data value from Current Year data table
                if (_trendTypes[i - 1] == "%")
                {
                    sheet.Cells[row + i - 1, col - 2].Value = "%";
                    actualFormula = $"Sheet1!${((char)('A' + 2 * i)).ToString()}${19 + _date.Month}";
                    targetFormula = $"Sheet1!${((char)('A' + 2 * i + 1)).ToString()}${19 + _date.Month}";
                }
                // Use 'ytd' metric if Previous year data is not available
                else if (_trendTypes[i - 1] == "ytd" || previousYearTarget == null)
                {
                    sheet.Cells[row + i - 1, col - 2].Value = "ytd";
                    targetFormula = $"SUM(Sheet1!${((char)('A' + 2 * i + 1)).ToString()}$20:" +
                        $"${((char)('A' + i * 2 + 1)).ToString()}${19 + _date.Month}) / SUM(Sheet1!$" +
                        $"{((char)('A' + i * 2 + 1)).ToString()}$20:" + $"${((char)('A' + i * 2 + 1)).ToString()}$" +
                        $"{31})";
                    actualFormula = $"SUM(Sheet1!${((char)('A' + 2 * i)).ToString()}$20:" +
                        $"${((char)('A' + i * 2)).ToString()}${19 + _date.Month}) / SUM(Sheet1!$" +
                        $"{((char)('A' + i * 2 + 1)).ToString()}$20:" + $"${((char)('A' + i * 2 + 1)).ToString()}$" +
                        $"{31})";
                }
                // Set formula according to 'yoy' metric
                else
                {
                    sheet.Cells[row + i - 1, col - 2].Value = "yoy";
                    actualFormula = $"(Sheet1!${((char)('A' + 2 * i)).ToString()}$" +
                        $"{19 + _date.Month} - Sheet1!${((char)('A' + 2 * i)).ToString()}${36 + _date.Month}) / " +
                        $"Sheet1!${((char)('A' + 2 * i)).ToString()}${36 + _date.Month}";
                    targetFormula = $"(Sheet1!${((char)('A' + 2 * i + 1)).ToString()}$" +
                        $"{19 + _date.Month} - Sheet1!{((char)('A' + 2 * i + 1)).ToString()}${36 + _date.Month}) / " +
                        $"Sheet1!${((char)('A' + 2 * i + 1)).ToString()}${36 + _date.Month}";
                }
                sheet.Cells[row + i - 1, col].Formula = actualFormula;
                sheet.Cells[row + i - 1, col + 1].Formula = targetFormula;
            }
        }

        private void LoadCircularIcons(SheetView sheet, int row, int col)
        {
            for (int i = 0; i < _kpiList.Items.Count; ++i)
            {
                UpdateCircularIcon(sheet, row + i, col);
            }
        }

        // Refresh Color of Circular icon (if it is shown)
        private void UpdateCircularIcon(SheetView sheet, int row, int col)
        {
            double actual = 0, target = 0;
            if (!double.TryParse(sheet.Cells[row, col + 8].Value.ToString(), out actual))
            {
                actual = 1;
                sheet.Cells[row, col + 8].Value = 1;
            }
            if (!double.TryParse(sheet.Cells[row, col + 9].Value.ToString(), out target))
            {
                target = 1;
                sheet.Cells[row, col + 9].Value = 1;
            }

            sheet.Cells[row, col].Value = _circularSymbol;
            // Orange Color : if actual value is below 95% of Target
            // Yellow Color : if actual value is within 5% of Target
            if (actual < 0.95 * target)
                sheet.Cells[row, col].ForeColor = Color.Orange;
            else if (actual >= 0.95 * target && actual <= 1.05 * target && actual != 0 && target != 0)
                sheet.Cells[row, col].ForeColor = Color.Yellow;
            else
                sheet.Cells[row, col].Value = string.Empty;
        }
        #endregion
    }
}


//
//BusinessKPI.Designer.cs
//


namespace GrapeCity.BusinessKPI
{
    partial class BusinessKPI
    {
        /// <summary> 
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary> 
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Component Designer generated code

        /// <summary> 
        /// Required method for Designer support - do not modify 
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.fpSpread = new FarPoint.Win.Spread.FpSpread(FarPoint.Win.Spread.LegacyBehaviors.None);
            this.fpSpread1_Sheet1 = new FarPoint.Win.Spread.SheetView();
            this.pnlMain.SuspendLayout();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread1_Sheet1)).BeginInit();
            this.SuspendLayout();
            // 
            // pnlMain
            // 
            this.pnlMain.Controls.Add(this.fpSpread);
            this.pnlMain.Size = new System.Drawing.Size(913, 510);
            // 
            // pnlControls
            // 
            this.pnlControls.Size = new System.Drawing.Size(302, 510);
            // 
            // fpSpread
            // 
            this.fpSpread.AccessibleDescription = "";
            this.fpSpread.Dock = System.Windows.Forms.DockStyle.Fill;
            this.fpSpread.Location = new System.Drawing.Point(0, 0);
            this.fpSpread.Name = "fpSpread";
            this.fpSpread.Sheets.AddRange(new FarPoint.Win.Spread.SheetView[] {
            this.fpSpread1_Sheet1});
            this.fpSpread.Size = new System.Drawing.Size(913, 510);
            this.fpSpread.TabIndex = 0;
            // 
            // fpSpread1_Sheet1
            // 
            this.fpSpread1_Sheet1.Reset();
            this.fpSpread1_Sheet1.SheetName = "Sheet1";
            // Formulas and custom names must be loaded with R1C1 reference style
            this.fpSpread1_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.R1C1;
            this.fpSpread1_Sheet1.ColumnFooter.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread1_Sheet1.ColumnFooter.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread1_Sheet1.ColumnFooterSheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread1_Sheet1.ColumnFooterSheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread1_Sheet1.ColumnHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread1_Sheet1.ColumnHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread1_Sheet1.RowHeader.DefaultStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread1_Sheet1.RowHeader.DefaultStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread1_Sheet1.SheetCornerStyle.BackColor = System.Drawing.Color.Empty;
            this.fpSpread1_Sheet1.SheetCornerStyle.ForeColor = System.Drawing.Color.Empty;
            this.fpSpread1_Sheet1.ReferenceStyle = FarPoint.Win.Spread.Model.ReferenceStyle.A1;
            // 
            // BusinessKPI
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.Name = "BusinessKPI";
            this.Size = new System.Drawing.Size(1216, 510);
            this.pnlMain.ResumeLayout(false);
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.fpSpread1_Sheet1)).EndInit();
            this.ResumeLayout(false);

        }

        #endregion

        private FarPoint.Win.Spread.FpSpread fpSpread;
        private FarPoint.Win.Spread.SheetView fpSpread1_Sheet1;
    }
}
